﻿-- DROP PROCEDURE [dbo].[Entity_InsertUpdate]

CREATE PROCEDURE [dbo].[Entity_InsertUpdate]

	@id					BIGINT,

	@name				VARCHAR (0060)		= NULL, 


	@entityType			INT, 

	@person				BIT,

	@nameLast			VARCHAR (0060)		= NULL,

	@nameFirst			VARCHAR (0060)		= NULL,

	@nameMiddle			VARCHAR (0060)		= NULL,

	@nameSuffix			VARCHAR (0060)		= NULL,


	@uniqueId			VARCHAR (0020)		= NULL,

	@federalTaxId		VARCHAR (0009),

	@legacyId			VARCHAR (0020),


	@modifiedId					VARCHAR (0060)
	
AS

	BEGIN 

		MERGE Entity

			USING (SELECT @id) AS Source (Id)

				ON Entity.Id = Source.Id

			WHEN MATCHED THEN 

				UPDATE SET 

					UniqueId = ISNULL (@uniqueId, Entity.Id),

					FederalTaxId = ISNULL (@federalTaxId, ''),

					LegacyId = ISNULL (@legacyId, ''),


					ModifiedId = @modifiedId,

					ModifiedDateTime = GETDATE ()

			WHEN NOT MATCHED THEN 

				INSERT (Name, EntityType, Person, NameLast, NameFirst, NameMiddle, NameSuffix, UniqueId, FederalTaxId, LegacyId, CreateId, CreateDateTime, ModifiedId, ModifiedDateTime) 

					VALUES (

						CASE WHEN (@person = 0) THEN @name ELSE NULL END, 

						@entityType, 

						@person, 

						CASE WHEN (@person = 1) THEN @nameLast ELSE NULL END, 

						CASE WHEN (@person = 1) THEN @nameFirst ELSE NULL END, 

						CASE WHEN (@person = 1) THEN @nameMiddle ELSE NULL END, 

						CASE WHEN (@person = 1) THEN @nameSuffix ELSE NULL END,

						ISNULL (@uniqueId, SUBSTRING (REPLACE (NEWID (), '-', ''), 13, 20)),

						ISNULL (@federalTaxId, ''),

						ISNULL (@legacyId, ''), 

						@modifiedId, GETDATE (), @modifiedId, GETDATE())

		; -- MERGE ( END )

		-- FOR NEW INSERTS, NEED TO WAIT UNTIL AFTER INSERT TO SET THE

		-- UNIQUE ID TO THE NEWLY GENERATED IDENTITY OF THE ENTITY

		IF ((@uniqueId IS NULL) AND (ISNULL (@id, 0) = 0))

			UPDATE Entity SET UniqueId = Id WHERE Id = @@IDENTITY

	END

	